Overview:
- A simple task often done by any database developer is to create a database and create a table underneath the database. Subsequently, the table is inserted with several rows and PostgreSQL is queried by an application program using a language like Python.
- A database acts as a container for a variety of SQL objects like tables, stored procedures and functions, indexes and views.
- CREATE DATABASE is one of the DDL statements supported by the PostgreSQL Database Management System.
- Remember, only a valid user of the PostgreSQL server does creation of any SQL object on a PostgreSQL server. The user should have sufficient privilege to create a database on PostgreSQL server.
- Psycopg2 is the Python client library for PostgreSQL. Using Psycopg2 a Python program can create a database on PostgreSQL server.
Creating a PostgreSQL database using Python and Psycopg2:
- Import the Psycopg2 module inside a Python program. If the import fails use the pip command to install Psycopg2.
- To install Psycopg2 use the command: pip install psycopg2
- Create a connection object to the PostgreSQL server by providing the logon credentials and calling the function psycopg2.connect().
- Through the connection object obtain a cursor object by calling the cursor() method on the connection object.
- Define a Python string, which contains the SQL command CREATE DATABASE.
- Pass the Python string containing the SQL statement while calling the execute() method of the database cursor.
- Calling execute() function with CREATE DATABASE creates a database on the PostgreSQL server.
- To get the list of databases, enter into the interactive terminal of PostgreSQL, by typing psql from command line. From psql type \list or \l to list all the databases.
Example:
|
# ----- Example Python program to create a database in PostgreSQL using Psycopg2 ----- # import the PostgreSQL client for Python import psycopg2 from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
# Connect to PostgreSQL DBMS con = psycopg2.connect("user=test password='test'"); con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);
# Obtain a DB Cursor cursor = con.cursor(); name_Database = "SocialMedia";
# Create table statement sqlCreateDatabase = "create database "+name_Database+";"
# Create a table in PostgreSQL database cursor.execute(sqlCreateDatabase); |
Output:
|
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+-------------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | ims | test | UTF8 | en_US.UTF-8 | en_US.UTF-8 | socialmedia | test | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | test | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/test + | | | | | test=CTc/test (6 rows) |